package com.matt.repository.impl;

import com.matt.domain.nursery.NurseryScheduleCheck;
import com.matt.domain.nursery.NurseryScheduleRecord;
import com.matt.foundation.query.NurseryScheduleCheckQuery;
import com.matt.foundation.utils.DateUtil;
import com.matt.foundation.vo.nursery.NurseryScheduleCheckVo;
import com.matt.foundation.vo.nursery.NurseryScheduleRecordVo;
import com.matt.repository.base.BaseNativeSqlRepository;
import com.matt.repository.base.NurseryScheduleCheckNativeRepository;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.math.BigInteger;
import java.util.Date;
import java.util.List;
import java.util.Objects;

/**
 * *******************************
 * Created by lch.
 * form com.matt
 * on 2018/12/05
 * *******************************
 */
@Slf4j
@Service
public class NurseryScheduleCheckNativeRepositoryImpl extends BaseNativeSqlRepository implements NurseryScheduleCheckNativeRepository {
    @Override
    public List<NurseryScheduleCheckVo> list(NurseryScheduleCheckQuery query) {
        String querySql = String.format("select nsc.id as id, nsc.created as created, nsc.deleted as deleted,nsc.classesId as classesId,nsc.status as status,nsc.areaId as areaId," +
                " nsc.checkStaffId as checkStaffId,nsc.checkStaffName as checkStaffName,nsc.updateStaffId as updateStaffId,nsc.updateStaffName as updateStaffName,nsc.updateDate as updateDate," +
                " nsc.beginDate as beginDate,nsc.endDate as endDate,cl.name as classesName" +
                " from nursery_schedule_check as nsc inner join classes as cl on nsc.classesId=cl.id where nsc.areaId='%s' ",query.getAreaId());
        if (query.getStatus() !=null){
            querySql +=String.format(" and nsc.status='%s'",query.getStatus());
        }
        if (Objects.nonNull(query.getClassName())) {
        	querySql += String.format(" and cl.name like concat('%s', '%s', '%s') ", "%", query.getClassName(), "%");
        }
        querySql +=String.format(" order by nsc.created desc");
        querySql += String.format(" limit %s, %s ", query.getPageNum() * query.getPageSize(), query.getPageSize());
        return sqlObjectList(querySql,NurseryScheduleCheckVo.class);
    }

    @Override
    public Long count(NurseryScheduleCheckQuery query) {
        String querySql = String.format("select (1) from nursery_schedule_check as nsc inner join classes as cl on nsc.classesId=cl.id where nsc.areaId='%s'",query.getAreaId());
        if (query.getStatus() !=null){
            querySql +=String.format(" and status='%s'",query.getStatus());
        }
        if (Objects.nonNull(query.getClassName())) {
        	querySql += String.format(" and cl.name like concat('%s', '%s', '%s') ", "%", query.getClassName(), "%");
        }
        List objectArrayList = sqlSingleList(querySql);
        if (objectArrayList.size()>0){
            return Long.parseLong(objectArrayList.size()+"");
        }
        return new Long(0);
    }

    @Override
    public NurseryScheduleCheck findByClassesIdCourseDate(String classesId, String courseDate) {
        String querySql = String.format("select nsc.id as id, nsc.created as created, nsc.deleted as deleted,nsc.classesId as classesId,nsc.status as status,nsc.areaId as areaId," +
                " nsc.checkStaffId as checkStaffId,nsc.checkStaffName as checkStaffName,nsc.updateStaffId as updateStaffId,nsc.updateStaffName as updateStaffName,nsc.updateDate as updateDate," +
                " nsc.beginDate as beginDate,nsc.endDate as endDate" +
                " from nursery_schedule_check as nsc where true and nsc.classesId='%s' and '%s'>=date(nsc.beginDate) and '%s'<=date(nsc.endDate)",classesId,courseDate,courseDate);
        List<NurseryScheduleCheck> list = sqlObjectList(querySql,NurseryScheduleCheck.class);
        if (list.size() > 0)
            return list.get(0) !=null ? list.get(0):null;
        return null;
    }

    @Override
    public List<NurseryScheduleRecordVo> findByClassesIdAndDate(String classesId, Date beginDate, Date endDate) {
        String querySql = String.format("select nsr.id as id,nsr.created as created,nsr.deleted as deleted,nsr.nurseryScheduleId as nurseryScheduleId," +
                "nsr.activityContent as activityContent,nsr.category as category,nsr.beginDate as beginDate,nsr.endDate as endDate,ns.lessonDate as lessonDate " +
                " from nursery_schedule_record as nsr inner join nursery_schedule as ns on nsr.nurseryScheduleId=ns.id " +
                " where ns.classesId='%s' and date(lessonDate)>=date('%s') and date(lessonDate)<=date('%s') order by nsr.beginDate ",classesId, DateUtil.convertStr(beginDate),DateUtil.convertStr(endDate));
        List<NurseryScheduleRecordVo> list = sqlObjectList(querySql,NurseryScheduleRecordVo.class);
        return list;
    }

    @Override
    public List<Object[]> getAreaIdAndClassesName(String checkId) {
        String querySql = String.format("select cl.name as classesName,cl.areaId as areaId,cl.id as classesId,nsc.beginDate as beginDate,nsc.endDate as endDate," +
                " nsc.status as status,nsc.created as created,nsc.updateStaffName as updateStaffName " +
                " from nursery_schedule_check nsc left join classes as cl on nsc.classesId=cl.id where nsc.id='%s'",checkId);
        List<Object[]> list = sqlArrayList(querySql);
        return list;
    }

    @Override
    public List<BigInteger> getBeginInfo(String classesId, Date beginDate, Date endDate) {
        String querySql= "SELECT count(DATE_FORMAT(nsr.beginDate, '%H:%i')) as beginDateCount from nursery_schedule_record as nsr left join nursery_schedule as ns on nsr.nurseryScheduleId=ns.id ";
                querySql +=" WHERE ns.classesId='"+classesId+"' and date(ns.lessonDate)>=date('"+DateUtil.convertStr(beginDate)+"') and date(ns.lessonDate)<=date('"+DateUtil.convertStr(endDate)+"')" ;
                 querySql+=" GROUP BY  DATE_FORMAT(nsr.beginDate, '%H:%i') HAVING beginDateCount>0";
        List<BigInteger> list = sqlSingleList(querySql);
       return list;
    }

    @Override
    public List<BigInteger> getEndInfo(String classesId, Date beginDate, Date endDate) {
        String querySql= "SELECT count(DATE_FORMAT(nsr.endDate, '%H:%i')) as endCount from nursery_schedule_record as nsr left join nursery_schedule as ns on nsr.nurseryScheduleId=ns.id " ;
                querySql +=" WHERE ns.classesId='"+classesId+"' and date(ns.lessonDate)>=date('"+DateUtil.convertStr(beginDate)+"') and date(ns.lessonDate)<=date('"+DateUtil.convertStr(endDate)+"')" ;
                querySql +=" GROUP BY  DATE_FORMAT(nsr.endDate, '%H:%i') HAVING endCount>0";
        List<BigInteger> list = sqlSingleList(querySql);
        return list;
    }

    @Override
    public List<BigInteger> getNurseryInfo(String classesId, Date beginDate, Date endDate) {
        String querySql = String.format("SELECT count(DISTINCT(ns.id)) as nuseryScheduleCount from nursery_schedule_record as nsr " +
                " inner join nursery_schedule as ns on nsr.nurseryScheduleId=ns.id " +
                " WHERE ns.classesId='%s'and date(ns.lessonDate)>=date('%s') and date(ns.lessonDate)<=date('%s')",classesId,DateUtil.convertStr(beginDate),DateUtil.convertStr(endDate));
        List<BigInteger> list = sqlSingleList(querySql);
        return list;
    }
}
